package top.yudoge.dogeeditor.db.service

import com.google.gson.Gson
import top.yudoge.dogeeditor.db.ResultSetUtil
import top.yudoge.dogeeditor.db.api.DataSourceMetaDataApi
import top.yudoge.dogeeditor.db.api.DataSourceVendorEnum
import top.yudoge.dogeeditor.db.api.TableTypeEnum
import top.yudoge.dogeeditor.db.datasource.DataSourceManager
import top.yudoge.dogeeditor.db.objects.*
import java.sql.Connection
import java.sql.SQLException
import java.sql.Statement

class DataSourceMetaDataService(
    private val dataSourceManager: DataSourceManager
) : DataSourceMetaDataApi {

    override fun getDataSources(): List<DataSource> {
        return dataSourceManager.listDataSourceConfig().map {
            DataSource(it.identifier, it.vendor)
        }
    }

    override fun getDatabases(dataSourceIdentifier: String): List<Database> {
        requireDSConnection(dataSourceIdentifier).use { connection ->
            val catalogs = connection.metaData.catalogs

            return buildList {
                while (catalogs.next()) {
                    add(Database(
                        dataSourceIdentifier,
                        ResultSetUtil.getStringValue(catalogs, "TABLE_CAT"),
                        ""
                    ))
                }
            }
        }
    }

    override fun getSchemas(dataSourceIdentifier: String, databaseName: String): List<Schema> {
        requireDSConnection(dataSourceIdentifier).use { connection ->
            val vendor = requireDSVendor(dataSourceIdentifier)
            return vendor.bridge.schemaList(connection, dataSourceIdentifier, databaseName)
        }
    }

    override fun getTables(dataSourceIdentifier: String, databaseName: String, schemaName: String): List<Table> {
        requireDSConnection(dataSourceIdentifier).use { connection ->
            val tables = connection.metaData.getTables(databaseName, schemaName, "%", TableTypeEnum.allJDBCCode())

            return buildList {
                while (tables.next()) {
                    val tableType = TableTypeEnum.byJDBCCode(tables.getString("TABLE_TYPE"))
                        ?: continue

                    add(Table(
                        dataSourceIdentifier,
                        databaseName,
                        schemaName,
                        ResultSetUtil.getStringValue(tables, "TABLE_NAME"),
                        ResultSetUtil.getStringValue(tables, "REMARKS"),
                        tableType
                    ))
                }
            }
        }
    }

    override fun getColumns(dataSourceIdentifier: String, databaseName: String, schemaName: String, tableName: String): List<Column> {
        requireDSConnection(dataSourceIdentifier).use { connection ->
            val columns = connection.metaData.getColumns(databaseName, schemaName, tableName, "%")

            return buildList {
                while (columns.next()) {
                    add(Column(
                        dataSourceIdentifier,
                        databaseName,
                        schemaName,
                        tableName,
                        ResultSetUtil.getStringValue(columns, "COLUMN_NAME"),
                        ResultSetUtil.getStringValue(columns, "TYPE_NAME"),
                        ResultSetUtil.getStringValue(columns, "REMARKS")
                    ))
                }
            }
        }
    }

    override fun getIndexes(dataSourceIdentifier: String, databaseName: String, schemaName: String, tableName: String): List<Index> {
        requireDSConnection(dataSourceIdentifier).use { connection ->
            val indexes = connection.metaData.getIndexInfo(databaseName, schemaName, tableName, false, false)
            return buildList {
                while (indexes.next()) {
                    add(Index(
                        dataSourceIdentifier,
                        databaseName,
                        schemaName,
                        tableName,
                        ResultSetUtil.getStringValue(indexes, "INDEX_NAME"),
                        ResultSetUtil.getStringValue(indexes, "COLUMN_NAME"),
                        ResultSetUtil.getBooleanValue(indexes, "NON_UNIQUE")
                    ))
                }
            }
        }

    }

    override fun executeSql(
        dataSourceIdentifier: String,
        databaseName: String,
        schemaName: String,
        sql: String
    ): SQLExecuteResult {
        val result = mutableListOf<Map<String, Any>>()
        try {
            requireDSConnection(dataSourceIdentifier).use { connection ->
                val vendor = requireDSVendor(dataSourceIdentifier)
                vendor.bridge.move(connection, databaseName, schemaName)
                connection.createStatement().use { statement: Statement? ->
                    var execute = statement?.executeQuery(sql)
                    execute?.use { resultSet ->
                        while (resultSet.next()) {
                            val row = mutableMapOf<String, Any>()
                            for (i in 1..resultSet.metaData.columnCount) {
                                row[resultSet.metaData.getColumnName(i)] = resultSet.getObject(i)
                            }
                            result.add(row)
                        }
                    }
                }
            }
        } catch (e: SQLException) {
            return SQLExecuteResult(false, e.message ?: "", null)
        }
        return SQLExecuteResult(true, "", Gson().toJson(result))
    }

    private fun requireDSConnection(dataSourceIdentifier: String): Connection {
        val dataSource = dataSourceManager.getDataSource(dataSourceIdentifier) ?:
            throw IllegalArgumentException("Unknown data source identifier ${dataSourceIdentifier}")
        return dataSource.connection
    }


    private fun requireDSVendor(dataSourceIdentifier: String): DataSourceVendorEnum {
        val dataSourceConfig = dataSourceManager.listDataSourceConfig()
            .firstOrNull { it.identifier == dataSourceIdentifier }
            ?: throw IllegalArgumentException("Unknown data source ${dataSourceIdentifier}")
        return dataSourceConfig.vendor
    }

}